﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BLL.Services.WikiCategories.Listing;

namespace BLL.Services.WikiCategories
{
    public class WikiCategoryCollection
    {
        private readonly string _connectionString;

        public WikiCategoryCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        public WikiCategory GetById(int wikiCategoryId)
        {
            WikiCategory result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiCategories.GetById(conn, wikiCategoryId);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result = GetWikiCategory(reader);
                    }
                }
            }
            return result;
        }

        public WikiCategoryListing GetWikiPagesListing(
            int page, int size, string column, int direction, WikiCategoryFilter filter)
        {
            var result = new WikiCategoryListing
                             {
                                 Page = page,
                                 Size = size,
                                 Column = column,
                                 Direction = direction,
                                 Filter = filter
                             };
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiCategories.GetListing(
                    conn, page, size, column, direction, filter.Name);
                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                                     {
                                         Direction =
                                             ParameterDirection
                                             .ReturnValue
                                     };
                cmd.Parameters.Add(totalCount);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new WikiCategoryListing.WikiCategory();
                        item.WikiCategoryId = reader.GetInt32(0);
                        item.Name = reader.GetString(1);
                        item.CountPages = reader.GetInt32(2);
                        result.WikiCategories.Add(item);
                    }
                }
                result.TotalPages = (int)totalCount.Value % size == 0
                                       ? (int)totalCount.Value / size
                                       : ((int)totalCount.Value / size) + 1;
            }
            return result;
        }

        public void Create(WikiCategory model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiCategories.Insert(conn, model.Name);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.HasRows)
                        {
                            model = GetWikiCategory(reader);
                        }
                    }
                }
            }
        }

        public void Save(WikiCategory model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiCategories.Update(
                    conn, model.WikiCategoryId, model.Name);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.HasRows)
                        {
                            model = GetWikiCategory(reader);
                        }
                    }
                }
            }
        }

        public void Delete(int wikiCategoryId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiCategories.Delete(conn, wikiCategoryId);
                cmd.ExecuteNonQuery();
            }
        }

        public bool ValidateWikiCategory(int wikiCategoryId, string name)
        {
            var result = false;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiCategories.Validate(conn, wikiCategoryId, name);

                var isValidParameter = new SqlParameter("@valid", SqlDbType.Bit, 4)
                                           {
                                               Direction = ParameterDirection.ReturnValue
                                           };
                cmd.Parameters.Add(isValidParameter);
                cmd.ExecuteNonQuery();
                result = (int)isValidParameter.Value == 1;
            }
            return result;
        }

        private WikiCategory GetWikiCategory(SqlDataReader reader)
        {
            var wikiCategory = new WikiCategory
                                   {
                                       WikiCategoryId = reader.GetInt32(0),
                                       Name = reader.GetString(1)
                                   };
            return wikiCategory;
        }

        public IEnumerable<WikiCategory> SelectAll()
        {
            var result = new List<WikiCategory>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiCategories.SelectAll(conn);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var wikiCategory = GetWikiCategory(reader);
                        result.Add(wikiCategory);
                    }
                }
            }
            return result;
        }
    }
}
